﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

/// <summary>
/// Summary description for BussinessDAL
/// </summary>
/// 
namespace nsDalLayer
{
   

        #region interface
        public interface intGroup
        {
            Int32 GroupID
            {
                get;
                set;
            }
            string GroupTitle
            {
                get;
                set;
            }
            string GroupDsc
            {
                get;
                set;
            }
            string GroupThumbPath
            {
                get;
                set;
            }
            string GroupImagePath
            {
                get;
                set;
            }
            Int32 Group_MainCatID
            {
                get;
                set;
            }
        }
        public interface intItem
        {
            Int32 ItemID
            {
                get;
                set;
            }
            string ItemTitle
            {
                get;
                set;
            }
            string ItemTag
            {
                get;
                set;
            }
            string ItemDsc
            {
                get;
                set;
            }
            string ItemThumbPath
            {
                get;
                set;
            }
            string ItemImagePath
            {
                get;
                set;
            }
            Int32 Item_GroupID
            {
                get;
                set;
            }
        }

        public interface intSubItem
        {
            Int32 SubItemID
            {
                get;
                set;
            }
            string SubItemTitle
            {
                get;
                set;
            }
            string SubItemTag
            {
                get;
                set;
            }
            string SubItemDsc
            {
                get;
                set;
            }
            string SubItemThumbPath
            {
                get;
                set;
            }
            string SubItemImagePath
            {
                get;
                set;
            }
            Int32 SubItem_ItemID
            {
                get;
                set;
            }
        }
        #endregion

        #region PropertyClasses
        public class clsItemPRP : intItem
        {
            int mItemID,mGroupID_Item;
            string mItemTitle, mItemTag, mItemDsc, mItemThumbPath, mImagePath;
            public int ItemID
            {
                get
                {
                    return mItemID;
                }
                set
                {
                    mItemID = value;
                }
            }

            public String ItemTag
            {
                get { return mItemTag; }
                set { mItemTag = value; }
            }

            public string ItemTitle
            {
                get
                {
                    return mItemTitle;
                }
                set
                {
                    mItemTitle = value;
                }
            }


            public string ItemDsc
            {
                get
                {
                    return mItemDsc;
                }
                set
                {
                    mItemDsc = value;
                }
            }

            public string ItemThumbPath
            {
                get
                {
                    return mItemThumbPath;
                }
                set
                {
                    mItemThumbPath = value;
                }
            }

            public string ItemImagePath
            {
                get
                {
                    return mImagePath;
                }
                set
                {
                    mImagePath = value;
                }
            }

            public int Item_GroupID
            {
                get
                {
                    return mItemID;
                }
                set
                {
                    ItemID = value;
                }
            }

           
        }

        public class clsSubItemPRP : intSubItem
        {
            int mSubItemID, mItemID_SubItem;
            string mSubItemTitle, mSubItemTag, mSubItemDsc, mSubItemThumbPath, mImagePath;
            public int SubItemID
            {
                get
                {
                    return mSubItemID;
                }
                set
                {
                    mSubItemID = value;
                }
            }

            public String SubItemTag
            {
                get { return mSubItemTag; }
                set { mSubItemTag = value; }
            }

            public string SubItemTitle
            {
                get
                {
                    return mSubItemTitle;
                }
                set
                {
                    mSubItemTitle = value;
                }
            }


            public string SubItemDsc
            {
                get
                {
                    return mSubItemDsc;
                }
                set
                {
                    mSubItemDsc = value;
                }
            }

            public string SubItemThumbPath
            {
                get
                {
                    return mSubItemThumbPath;
                }
                set
                {
                    mSubItemThumbPath = value;
                }
            }

            public string SubItemImagePath
            {
                get
                {
                    return mImagePath;
                }
                set
                {
                    mImagePath = value;
                }
            }

            public int SubItem_ItemID
            {
                get
                {
                    return mSubItemID;
                }
                set
                {
                    SubItemID = value;
                }
            }


        }

        public class clsGroupPRP : intGroup
        {
            int mGroupID, mMainCatID;
            string mGroupTitle, mGroupDsc, mGroupThumbPath,mGroupImagePath, mMainCatID_Group;
            public int GroupID
            {
                get
                {
                    return mGroupID;
                }
                set
                {
                    mGroupID = value;
                }
            }

            public string GroupTitle
            {
                get
                {
                    return mGroupTitle;
                }
                set
                {
                    mGroupTitle = value;
                }
            }


            public string GroupDsc
            {
                get
                {
                    return mGroupDsc;
                }
                set
                {
                    mGroupDsc = value;
                }
            }

            public string GroupThumbPath
            {
                get
                {
                    return mGroupThumbPath;
                }
                set
                {
                    mGroupThumbPath = value;
                }
            }

            public string GroupImagePath
            {
                get
                {
                    return mGroupImagePath;
                }
                set
                {
                    mGroupImagePath = value;
                }
            }

            public int Group_MainCatID
            {
                get
                {
                    return mGroupID;
                }
                set
                {
                    GroupID = value;
                }
            }
        }
        #endregion

        #region SQLConnection
        public abstract class clscon
        {
            protected SqlConnection con = new SqlConnection();
            public clscon()
            {
                con.ConnectionString = ConfigurationManager.ConnectionStrings["cn"].ConnectionString;
            }

        }
        #endregion

        #region Groups
        public class clsGroup : clscon
        {
            public void save_rec(clsGroupPRP p)
            {

                if (con.State == ConnectionState.Closed)
                {
                    con.Open();
                }
                //SqlCommand cmd = new SqlCommand("inscnt", con);
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "Save_Group";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = con;
                cmd.Parameters.Add("@GroupTitle", SqlDbType.VarChar, 50).Value = p.GroupTitle;
                cmd.Parameters.Add("@GroupDsc", SqlDbType.VarChar, 50).Value = p.GroupDsc;
                cmd.Parameters.Add("@GroupThumbPath", SqlDbType.VarChar, 50).Value = p.GroupThumbPath;
                cmd.Parameters.Add("@GroupImagePath", SqlDbType.VarChar, 50).Value = p.GroupImagePath;
                cmd.Parameters.Add("@MainCatID", SqlDbType.VarChar, 50).Value = p.Group_MainCatID;
                cmd.ExecuteNonQuery();
                cmd.Dispose();

            }
            public void Update_rec(clsGroupPRP p)
            {

                if (con.State == ConnectionState.Closed)
                {
                    con.Open();
                }
                //SqlCommand cmd = new SqlCommand("inscnt", con);
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "Update_Group";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = con;
                cmd.Parameters.Add("@GroupID", SqlDbType.VarChar, 50).Value = p.GroupID;
                cmd.Parameters.Add("@GroupTitle", SqlDbType.VarChar, 50).Value = p.GroupTitle;
                cmd.Parameters.Add("@GroupDsc", SqlDbType.VarChar, 50).Value = p.GroupDsc;
                cmd.Parameters.Add("@GroupThumbPath", SqlDbType.VarChar, 50).Value = p.GroupThumbPath;
                cmd.Parameters.Add("@GroupImagePath", SqlDbType.VarChar, 50).Value = p.GroupImagePath;
                cmd.Parameters.Add("@MainCatID", SqlDbType.VarChar, 50).Value = p.Group_MainCatID;
                cmd.ExecuteNonQuery();
                cmd.Dispose();

            }
            public void Delete_rec(clsGroupPRP p)
            {

                if (con.State == ConnectionState.Closed)
                {
                    con.Open();
                }
                //SqlCommand cmd = new SqlCommand("inscnt", con);
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "Delete_Group";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = con;
                cmd.Parameters.Add("@GroupID", SqlDbType.VarChar, 50).Value = p.GroupID;
                cmd.ExecuteNonQuery();
                cmd.Dispose();

            }
            public List<clsGroupPRP> disp_rec()
            {
                if (con.State == ConnectionState.Closed)
                {
                    con.Open();
                }
                // SqlCommand cmd = new SqlCommand("dspcnt", con);
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "Select_Group";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = con;
                SqlDataReader dr = cmd.ExecuteReader();
                List<clsGroupPRP> obj = new List<clsGroupPRP>();
                while (dr.Read())
                {
                    clsGroupPRP k = new clsGroupPRP();
                    k.GroupID = Convert.ToInt32(dr[0]);
                    k.GroupTitle = dr[1].ToString();
                    k.GroupDsc = dr[2].ToString();
                    k.GroupThumbPath = dr[3].ToString();
                    k.GroupImagePath = dr[4].ToString();
                    k.Group_MainCatID = Convert.ToInt32(dr[5]);
                    obj.Add(k);
                }
                dr.Close();
                cmd.Dispose();
                con.Close();
                return obj;
            }

            public List<clsGroupPRP> LastInserted_Group()
            {
                if (con.State == ConnectionState.Closed)
                {
                    con.Open();
                }
                // SqlCommand cmd = new SqlCommand("dspcnt", con);
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "LastInserted_Group";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = con;
                SqlDataReader dr = cmd.ExecuteReader();
                List<clsGroupPRP> obj = new List<clsGroupPRP>();
                while (dr.Read())
                {
                    clsGroupPRP k = new clsGroupPRP();
                    k.GroupID = Convert.ToInt32(dr[0]);
                    k.GroupTitle = dr[1].ToString();
                    k.GroupDsc = dr[2].ToString();
                    k.GroupThumbPath = dr[3].ToString();
                    k.GroupImagePath = dr[4].ToString();
                    k.Group_MainCatID = Convert.ToInt32(dr[5]);
                    obj.Add(k);
                }
                dr.Close();
                cmd.Dispose();
                con.Close();
                return obj;
            }
            public List<clsGroupPRP> find_rec(Int32 groupID)
            {
                if (con.State == ConnectionState.Closed)
                {
                    con.Open();
                }
                SqlCommand cmd = new SqlCommand("FindGroup", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@GroupID", SqlDbType.Int).Value = groupID;
                SqlDataReader dr = cmd.ExecuteReader();
                List<clsGroupPRP> obj = new List<clsGroupPRP>();
                if (dr.HasRows)
                {
                    dr.Read();
                    clsGroupPRP k = new clsGroupPRP();
                    k.GroupID = Convert.ToInt32(dr[0]);
                    k.GroupTitle = dr[1].ToString();
                    k.GroupDsc = dr[2].ToString();
                    k.GroupThumbPath = dr[3].ToString();
                    k.GroupImagePath = dr[4].ToString();
                    k.Group_MainCatID = Convert.ToInt32(dr[5]);
                    obj.Add(k);
                }
                dr.Close();
                cmd.Dispose();
                con.Close();
                return obj;
            }

        }
        #endregion

        #region Items
        public class clsItem : clscon
        {
            public void save_rec(clsItemPRP p)
            {

                if (con.State == ConnectionState.Closed)
                {
                    con.Open();
                }
                //SqlCommand cmd = new SqlCommand("inscnt", con);
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "Save_Item";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = con;
                cmd.Parameters.Add("@ItemTitle", SqlDbType.VarChar, 50).Value = p.ItemTitle;
                cmd.Parameters.Add("@ItemTag", SqlDbType.VarChar, 50).Value = p.ItemTag;
                cmd.Parameters.Add("@ItemDsc", SqlDbType.VarChar, 50).Value = p.ItemDsc;
                cmd.Parameters.Add("@ItemThumbPath", SqlDbType.VarChar, 50).Value = p.ItemThumbPath;
                cmd.Parameters.Add("@ItemImagePath", SqlDbType.VarChar, 50).Value = p.ItemImagePath;
                cmd.Parameters.Add("@GroupID", SqlDbType.VarChar, 50).Value = p.Item_GroupID;
                cmd.ExecuteNonQuery();
                cmd.Dispose();

            }
            public void Update_rec(clsItemPRP p)
            {

                if (con.State == ConnectionState.Closed)
                {
                    con.Open();
                }
                //SqlCommand cmd = new SqlCommand("inscnt", con);
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "Update_Item";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = con;
                cmd.Parameters.Add("@ItemID", SqlDbType.VarChar, 50).Value = p.ItemID;
                cmd.Parameters.Add("@ItemTitle", SqlDbType.VarChar, 50).Value = p.ItemTitle;
                cmd.Parameters.Add("@ItemDsc", SqlDbType.VarChar, 50).Value = p.ItemDsc;
                cmd.Parameters.Add("@ItemThumbPath", SqlDbType.VarChar, 50).Value = p.ItemThumbPath;
                cmd.Parameters.Add("@ItemImagePath", SqlDbType.VarChar, 50).Value = p.ItemImagePath;
                cmd.Parameters.Add("@GroupID", SqlDbType.VarChar, 50).Value = p.Item_GroupID;
                cmd.ExecuteNonQuery();
                cmd.Dispose();

            }
            public void Delete_rec(clsItemPRP p)
            {

                if (con.State == ConnectionState.Closed)
                {
                    con.Open();
                }
                //SqlCommand cmd = new SqlCommand("inscnt", con);
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "Delete_Item";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = con;
                cmd.Parameters.Add("@GroupID", SqlDbType.VarChar, 50).Value = p.Item_GroupID;
                cmd.ExecuteNonQuery();
                cmd.Dispose();

            }
            public List<clsItemPRP> disp_rec()
            {
                if (con.State == ConnectionState.Closed)
                {
                    con.Open();
                }
                // SqlCommand cmd = new SqlCommand("dspcnt", con);
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "Select_Item";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = con;
                SqlDataReader dr = cmd.ExecuteReader();
                List<clsItemPRP> obj = new List<clsItemPRP>();
                while (dr.Read())
                {
                    clsItemPRP k = new clsItemPRP();
                    k.ItemID = Convert.ToInt32(dr[0]);
                    k.ItemTitle = dr[1].ToString();
                    k.ItemDsc = dr[2].ToString();
                    k.ItemThumbPath = dr[3].ToString();
                    k.ItemImagePath = dr[4].ToString();
                   // k.Item_GroupID = Convert.ToInt32(dr[5]);
                    obj.Add(k);
                }
                dr.Close();
                cmd.Dispose();
                con.Close();
                return obj;
            }

            public List<clsItemPRP> LastInserted_Item()
            {
                if (con.State == ConnectionState.Closed)
                {
                    con.Open();
                }
                // SqlCommand cmd = new SqlCommand("dspcnt", con);
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "LastInserted_Item";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = con;
                SqlDataReader dr = cmd.ExecuteReader();
                List<clsItemPRP> obj = new List<clsItemPRP>();
                while (dr.Read())
                {
                    clsItemPRP k = new clsItemPRP();
                    k.ItemID = Convert.ToInt32(dr[0]);
                    k.ItemTitle = dr[1].ToString();
                    k.ItemDsc = dr[2].ToString();
                    k.ItemThumbPath = dr[3].ToString();
                    k.ItemImagePath = dr[4].ToString();
                    // k.Item_GroupID = Convert.ToInt32(dr[5]);
                    obj.Add(k);
                }
                dr.Close();
                cmd.Dispose();
                con.Close();
                return obj;
            }
            public List<clsItemPRP> find_rec(Int32 ItemID)
            {
                if (con.State == ConnectionState.Closed)
                {
                    con.Open();
                }
                SqlCommand cmd = new SqlCommand("FindItem", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ItemID", SqlDbType.Int).Value = ItemID;
                SqlDataReader dr = cmd.ExecuteReader();
                List<clsItemPRP> obj = new List<clsItemPRP>();
                if (dr.HasRows)
                {
                    dr.Read();
                    clsItemPRP k = new clsItemPRP();
                    k.ItemID = Convert.ToInt32(dr[0]);
                    k.ItemTitle = dr[1].ToString();
                    k.ItemDsc = dr[2].ToString();
                    k.ItemThumbPath = dr[3].ToString();
                    k.ItemImagePath = dr[4].ToString();
                    k.Item_GroupID = Convert.ToInt32(dr[5]);
                    obj.Add(k);
                }
                dr.Close();
                cmd.Dispose();
                con.Close();
                return obj;
            }

        }
        #endregion

        #region SubItems
        public class clsSubItem : clscon
        {
            public void save_rec(clsSubItemPRP p)
            {

                if (con.State == ConnectionState.Closed)
                {
                    con.Open();
                }
                //SqlCommand cmd = new SqlCommand("inscnt", con);
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "Save_SubItem";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = con;
                cmd.Parameters.Add("@SubItemTitle", SqlDbType.VarChar, 50).Value = p.SubItemTitle;
                cmd.Parameters.Add("@SubItemTag", SqlDbType.VarChar, 50).Value = p.SubItemTag;
                cmd.Parameters.Add("@SubItemDsc", SqlDbType.VarChar, 50).Value = p.SubItemDsc;
                cmd.Parameters.Add("@SubItemThumbPath", SqlDbType.VarChar, 50).Value = p.SubItemThumbPath;
                cmd.Parameters.Add("@SubItemImagePath", SqlDbType.VarChar, 50).Value = p.SubItemImagePath;
                cmd.Parameters.Add("@ItemID", SqlDbType.VarChar, 50).Value = p.SubItem_ItemID;
                cmd.ExecuteNonQuery();
                cmd.Dispose();

            }
            public void Update_rec(clsSubItemPRP p)
            {

                if (con.State == ConnectionState.Closed)
                {
                    con.Open();
                }
                //SqlCommand cmd = new SqlCommand("inscnt", con);
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "Update_SubItem";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = con;
                cmd.Parameters.Add("@SubItemID", SqlDbType.VarChar, 50).Value = p.SubItemID;
                cmd.Parameters.Add("@SubItemTitle", SqlDbType.VarChar, 50).Value = p.SubItemTitle;
                cmd.Parameters.Add("@SubItemTag", SqlDbType.VarChar, 50).Value = p.SubItemTag;
                cmd.Parameters.Add("@SubItemDsc", SqlDbType.VarChar, 50).Value = p.SubItemDsc;
                cmd.Parameters.Add("@SubItemThumbPath", SqlDbType.VarChar, 50).Value = p.SubItemThumbPath;
                cmd.Parameters.Add("@SubItemImagePath", SqlDbType.VarChar, 50).Value = p.SubItemImagePath;
                cmd.Parameters.Add("@ItemID", SqlDbType.VarChar, 50).Value = p.SubItem_ItemID;
                cmd.ExecuteNonQuery();
                cmd.Dispose();

            }
            public void Delete_rec(clsSubItemPRP p)
            {

                if (con.State == ConnectionState.Closed)
                {
                    con.Open();
                }
                //SqlCommand cmd = new SqlCommand("inscnt", con);
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "Delete_SubItem";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = con;
                cmd.Parameters.Add("@SubItemID", SqlDbType.VarChar, 50).Value = p.SubItemID;
                cmd.ExecuteNonQuery();
                cmd.Dispose();

            }
            public List<clsSubItemPRP> disp_rec()
            {
                if (con.State == ConnectionState.Closed)
                {
                    con.Open();
                }
                // SqlCommand cmd = new SqlCommand("dspcnt", con);
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "Select_SubItem";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = con;
                SqlDataReader dr = cmd.ExecuteReader();
                List<clsSubItemPRP> obj = new List<clsSubItemPRP>();
                while (dr.Read())
                {
                    clsSubItemPRP k = new clsSubItemPRP();
                    k.SubItemID = Convert.ToInt32(dr[0]);
                    k.SubItemTitle = dr[1].ToString();
                    k.SubItemDsc = dr[2].ToString();
                    k.SubItemThumbPath = dr[3].ToString();
                    k.SubItemImagePath = dr[4].ToString();
                    // k.SubItem_GroupID = Convert.ToInt32(dr[5]);
                    obj.Add(k);
                }
                dr.Close();
                cmd.Dispose();
                con.Close();
                return obj;
            }

            public List<clsSubItemPRP> LastInserted_SubItem()
            {
                if (con.State == ConnectionState.Closed)
                {
                    con.Open();
                }
                // SqlCommand cmd = new SqlCommand("dspcnt", con);
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "LastInserted_SubItem";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = con;
                SqlDataReader dr = cmd.ExecuteReader();
                List<clsSubItemPRP> obj = new List<clsSubItemPRP>();
                while (dr.Read())
                {
                    clsSubItemPRP k = new clsSubItemPRP();
                    k.SubItemID = Convert.ToInt32(dr[0]);
                    k.SubItemTitle = dr[1].ToString();
                    k.SubItemDsc = dr[2].ToString();
                    k.SubItemThumbPath = dr[3].ToString();
                    k.SubItemImagePath = dr[4].ToString();
                    // k.SubItem_GroupID = Convert.ToInt32(dr[5]);
                    obj.Add(k);
                }
                dr.Close();
                cmd.Dispose();
                con.Close();
                return obj;
            }
            public List<clsSubItemPRP> find_rec(Int32 SubItemID)
            {
                if (con.State == ConnectionState.Closed)
                {
                    con.Open();
                }
                SqlCommand cmd = new SqlCommand("FindSubItem", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@SubItemID", SqlDbType.Int).Value = SubItemID;
                SqlDataReader dr = cmd.ExecuteReader();
                List<clsSubItemPRP> obj = new List<clsSubItemPRP>();
                if (dr.HasRows)
                {
                    dr.Read();
                    clsSubItemPRP k = new clsSubItemPRP();
                    k.SubItemID = Convert.ToInt32(dr[0]);
                    k.SubItemTitle = dr[1].ToString();
                    k.SubItemDsc = dr[2].ToString();
                    k.SubItemThumbPath = dr[3].ToString();
                    k.SubItemImagePath = dr[4].ToString();
                    obj.Add(k);
                }
                dr.Close();
                cmd.Dispose();
                con.Close();
                return obj;
            }

        }
        #endregion

}